﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using DiscoveryLogic.Common.Utilities;
using DiscoveryLogic.Common.Utilities.OpenXml;
using DiscoveryLogic.DataViz.NetworkDiagram.NetworkData;
using DiscoveryLogic.WindowsUserControl.Dialogs;
using EntityType=DiscoveryLogic.DataViz.NetworkDiagram.NetworkData.EntityType;

namespace DiscoveryLogic.DataViz.NetworkDiagram.LinkerControls
{
    /// <summary>
    /// 
    /// </summary>
    public partial class Explorer : Form
    {
        #region events
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fromNetworkName"></param>
        /// <param name="toNetworkName"></param>
        /// <param name="join"></param>
        public delegate void OnObjectExplored(string fromNetworkName, string toNetworkName, NetworkJoin join);
        /// <summary>
        /// 
        /// </summary>
        public event OnObjectExplored ObjectExplored;
        #endregion

        #region fields
        private readonly string _NetworkName;
        private readonly NetworkDataObject _NetDO;
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="networkName"></param>
        /// <param name="netDO"></param>
        public Explorer(string networkName, NetworkDataObject netDO)
        {
            this._NetworkName = networkName;
            this._NetDO = netDO;
            InitializeComponent();
        }

        private void Explorer_Load(object sender, EventArgs e)
        {
            this.cbo_FromMember.Items.Clear();
            if (this._NetDO.LinkableFields != null && this._NetDO.LinkableFields.Count > 0)
            {
                foreach (string propName in this._NetDO.LinkableFields)
                {
                    this.cbo_FromMember.Items.Add(propName);
                }
            }

            Assembly assembly = this.GetType().Assembly;
            Type[] types = assembly.GetTypes();
            this.cbo_DistanceCalc.Items.Clear();
            foreach (Type type in types)
            {
                if (type.GetInterface(typeof(ILinkDistanceCalculator).FullName) != null)
                {
                    this.cbo_DistanceCalc.Items.Add(type.FullName);
                }
            }

            this.cbo_NodeTypes.Items.Clear();
            for (int i = 0; i <= 3; i++)
            {
                EntityType entityType = (EntityType)i;
                this.cbo_NodeTypes.Items.Add(entityType.ToString());
            }

            this.cbo_LinkTypes.Items.Clear();
            for (int i = 0; i <= 2; i++)
            {
                LinkType linkType = (LinkType)i;
                this.cbo_LinkTypes.Items.Add(linkType.ToString());
            }

            this.cbo_LinkMethod.Items.Clear();
            for (int i = 0; i <= 8; i++)
            {
                LinkMethod method = (LinkMethod)i;
                this.cbo_LinkMethod.Items.Add(method.ToString());
            }

            this.cbo_LinkBehavior.Items.Clear();
            for (int i = 0; i <= 3; i++)
            {
                NetworkLinkBehavior behavior = (NetworkLinkBehavior)i;
                this.cbo_LinkBehavior.Items.Add(behavior.ToString());
            }

            this.cbo_Linkers.Items.Clear();
            foreach (Type type in types)
            {
                if (type.GetInterface(typeof(INetworkJoinCalcuator).FullName) != null)
                {
                    this.cbo_Linkers.Items.Add(type.FullName);
                }
            }
        }

        #region source
        private void rd_Db_CheckedChanged(object sender, EventArgs e)
        {
            SetDataSourceType srcType = SetDataSourceType.DB;
            if (this.rd_Db.Checked)
                srcType = SetDataSourceType.DB;
            else if (this.rd_Xml.Checked)
                srcType = SetDataSourceType.XML;
            else if (this.rd_Excel.Checked)
                srcType = SetDataSourceType.ExcelFile;
            this.UpdateDataSourceType(srcType);
        }
        
        private void rd_Xml_CheckedChanged(object sender, EventArgs e)
        {
            SetDataSourceType srcType = SetDataSourceType.DB;
            if (this.rd_Db.Checked)
                srcType = SetDataSourceType.DB;
            else if (this.rd_Xml.Checked)
                srcType = SetDataSourceType.XML;
            else if (this.rd_Excel.Checked)
                srcType = SetDataSourceType.ExcelFile;
            this.UpdateDataSourceType(srcType);
        }

        private void rd_Excel_CheckedChanged(object sender, EventArgs e)
        {
            SetDataSourceType srcType = SetDataSourceType.DB;
            if (this.rd_Db.Checked)
                srcType = SetDataSourceType.DB;
            else if (this.rd_Xml.Checked)
                srcType = SetDataSourceType.XML;
            else if (this.rd_Excel.Checked)
                srcType = SetDataSourceType.ExcelFile;
            this.UpdateDataSourceType(srcType);
        }

        private void UpdateDataSourceType(SetDataSourceType srcType)
        {
            if (srcType == SetDataSourceType.DB)
            {
                this.cbo_DbProvider.Enabled = true;
                this.ck_UseSQL.Enabled = true;
                this.lbl_DataSourcePath.Text = "Connection:";
                this.lbl_RecordPath.Text = "Table:";
                this.btn_BrowseDataSource.Visible = false;
            }
            else if (srcType == SetDataSourceType.XML || srcType == SetDataSourceType.ExcelFile)
            {
                this.cbo_DbProvider.Enabled = false;
                this.ck_UseSQL.Enabled = false;
                this.rt_SQL.Enabled = false;
                this.lbl_DataSourcePath.Text = "Source file:";
                this.lbl_RecordPath.Text = "Record path:";
                this.btn_BrowseDataSource.Visible = true;
            }
        }

        private void btn_BrowseDataSource_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            if (this.rd_Xml.Checked)
            {
                dlg.Filter = "Xml files(*.find;*.xml)|*.find;*.xml|All files(*.*)|*.*";
            }
            else if (this.rd_Excel.Checked)
            {
                dlg.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
            }
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.txt_DataSourcePath.Text = dlg.FileName;
            }
        }

        private void ck_UseSQL_CheckedChanged(object sender, EventArgs e)
        {
            if(this.ck_UseSQL.Checked)
            {
                this.rt_SQL.Enabled = true;
                this.cbo_RecordPath.Enabled = false;
                this.btn_GetFields.Enabled = true;
            }
            else
            {
                this.rt_SQL.Enabled = false;
                this.cbo_RecordPath.Enabled = true;
                this.btn_GetFields.Enabled = false;
            }
        }

        private void btn_RefreshRecordPaths_Click(object sender, EventArgs e)
        {
            this.UpdateRecordPaths();
        }

        private void UpdateRecordPaths()
        {
            if (this.rd_Db.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                this.cbo_RecordPath.Items.Clear();
                string[] tblNames = DbUtil.GetTableNames(DataProviderType.MSSQL, string.Empty, this.txt_DataSourcePath.Text);
                if (tblNames != null && tblNames.Length > 0)
                {
                    foreach (string tblName in tblNames)
                    {
                        this.cbo_RecordPath.Items.Add(tblName);
                    }
                }
            }
            else if (this.rd_Xml.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                XmlTextReader xReader = new XmlTextReader(File.OpenRead(this.txt_DataSourcePath.Text));
                this.cbo_RecordPath.Items.Clear();
                List<string> allPaths = new List<string>();
                int lastDepth = -1;
                string lastNodeName = string.Empty;
                Stack<string> elementNames = new Stack<string>();
                while (xReader.Read())
                {
                    if (xReader.NodeType == XmlNodeType.Element)
                    {
                        int currentDepth = xReader.Depth;
                        if (currentDepth > lastDepth)
                        {
                            lastDepth = currentDepth;
                            lastNodeName = xReader.Name;
                            elementNames.Push(lastNodeName);
                            string xPath = this.GetXPath(elementNames);
                            if (!allPaths.Contains(xPath))
                                allPaths.Add(xPath);
                        }
                        else if (currentDepth == lastDepth)
                        {
                            if (xReader.Name == lastNodeName)
                                continue;
                            else
                            {
                                lastNodeName = xReader.Name;
                                if (elementNames.Count > 0)
                                    elementNames.Pop();
                                elementNames.Push(lastNodeName);
                                string xPath = this.GetXPath(elementNames);
                                if (!allPaths.Contains(xPath))
                                    allPaths.Add(xPath);
                            }
                        }
                        else
                        {
                            lastDepth = xReader.Depth;
                            lastNodeName = xReader.Name;
                            while (elementNames.Count > xReader.Depth)
                                elementNames.Pop();
                            elementNames.Push(lastNodeName);
                            string xPath = this.GetXPath(elementNames);
                            if (!allPaths.Contains(xPath))
                                allPaths.Add(xPath);
                        }
                    }
                    else if (xReader.NodeType == XmlNodeType.EndElement)
                    {

                    }
                }
                xReader.Close();
                foreach (string xPath in allPaths)
                {
                    this.cbo_RecordPath.Items.Add(xPath);
                }
            }
            else if (this.rd_Excel.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                //List<string> sheetNames = ExcelFileHelper.GetWorksheetNames(this.txt_DataSourcePath.Text);
                List<string> sheetNames = ExcelReader.GetWorksheetNames(this.txt_DataSourcePath.Text);
                this.cbo_RecordPath.Items.Clear();
                foreach (string sheetName in sheetNames)
                {
                    this.cbo_RecordPath.Items.Add(sheetName);
                }
            }
        }

        private void cbo_RecordPath_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cbo_RecordPath.SelectedIndex < 0)
                return;
            
            this.PopulateSourceFields();
        }

        private void PopulateSourceFields()
        {
            List<string> fieldNames = new List<string>();
            if (this.rd_Db.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                if (this.ck_UseSQL.Checked && !string.IsNullOrEmpty(this.rt_SQL.Text))
                {
                    DataProviderType dbProvider = DataProviderType.MSSQL;
                    if (this.cbo_DbProvider.SelectedIndex == 1)
                        dbProvider = DataProviderType.Oracle;
                    TableColumn[] cols = DbUtil.GetTableColumns(
                        dbProvider, this.txt_DataSourcePath.Text,
                        this.rt_SQL.Text);
                    if (cols != null && cols.Length > 0)
                    {
                        foreach(TableColumn col in cols)
                        {
                            fieldNames.Add(col.ColumnName);
                        }
                    }
                }
                else
                {
                    string tblName = this.cbo_RecordPath.SelectedItem.ToString();
                    TableColumn[] cols = DbUtil.GetTableColumns(
                        DataProviderType.MSSQL, this.txt_DataSourcePath.Text, string.Empty,
                        tblName);
                    if (cols != null && cols.Length > 0)
                    {
                        foreach (TableColumn col in cols)
                        {
                            fieldNames.Add(col.ColumnName);
                        }
                    }
                }
            }
            else if (this.rd_Xml.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                XmlDocument xDoc = new XmlDocument();
                xDoc.Load(this.txt_DataSourcePath.Text);
                XmlNodeList xNodes = xDoc.SelectNodes(this.cbo_RecordPath.SelectedItem.ToString());
                if (xNodes != null && xNodes.Count > 0)
                {
                    XmlNode xNode = xNodes[0];
                    foreach (XmlAttribute attr in xNode.Attributes)
                    {
                        fieldNames.Add(attr.Name);
                    }
                }
            }
            else if (this.rd_Excel.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
            {
                string sheetName = this.cbo_RecordPath.SelectedItem.ToString();
                // List<string> colHeaders = ExcelFileHelper.ReadColumnHeaders(this.txt_DataSourcePath.Text, sheetName);
                List<string> colHeaders = ExcelReader.ReadColumnHeaders(this.txt_DataSourcePath.Text, sheetName);
                if (colHeaders != null && colHeaders.Count > 0)
                {
                    foreach (string colName in colHeaders)
                    {
                        fieldNames.Add(colName);
                    }
                }
            }

            this.cbo_Key.Items.Clear();
            this.cbo_TitleField.Items.Clear();
            this.cbo_FilterField.Items.Clear();
            this.cbo_LinkFrom.Items.Clear();
            this.cbo_LinkTo.Items.Clear();
            this.dv_Fields.Rows.Clear();

            if(fieldNames.Count>0)
            {
                foreach (string colName in fieldNames)
                {
                    this.cbo_Key.Items.Add(colName);
                    this.cbo_TitleField.Items.Add(colName);
                    this.cbo_FilterField.Items.Add(colName);
                    this.cbo_LinkFrom.Items.Add(colName);
                    this.cbo_LinkTo.Items.Add(colName);

                    DataGridViewRow dr=new DataGridViewRow();
                    DataGridViewCheckBoxCell cellSelect=new DataGridViewCheckBoxCell();
                    cellSelect.Value = true;
                    dr.Cells.Add(cellSelect);
                    DataGridViewTextBoxCell cellField=new DataGridViewTextBoxCell();
                    cellField.Value = colName;
                    dr.Cells.Add(cellField);
                    DataGridViewComboBoxCell cellDataType=new DataGridViewComboBoxCell();
                    for(int i=0;i<=9;i++)
                    {
                        SimpleDataType dataType = (SimpleDataType) i;
                        cellDataType.Items.Add(dataType.ToString());
                    }
                    cellDataType.Value = SimpleDataType.TextType.ToString();
                    dr.Cells.Add(cellDataType);
                    DataGridViewCheckBoxCell cellFilterable=new DataGridViewCheckBoxCell();
                    cellFilterable.Value = true;
                    dr.Cells.Add(cellFilterable);
                    DataGridViewCheckBoxCell cellLinkable=new DataGridViewCheckBoxCell();
                    cellLinkable.Value = false;
                    dr.Cells.Add(cellLinkable);
                    this.dv_Fields.Rows.Add(dr);
                }

                if (this.cbo_DistanceCalc.SelectedIndex >= 0)
                {
                    string typeName = this.cbo_DistanceCalc.SelectedItem.ToString();
                    Assembly assembly = this.GetType().Assembly;
                    Type type = assembly.GetType(typeName);
                    if (type.GetInterface(typeof(ILinkDistanceCalculator).FullName) != null)
                    {
                        ILinkDistanceCalculator calc = (ILinkDistanceCalculator)Activator.CreateInstance(type);
                        this.PopulateDistanceCalcArgFields(calc, fieldNames);
                    }
                }
            }
        }

        private string GetXPath(Stack<string> elementNames)
        {
            string[] elementNamesCopy = elementNames.ToArray();
            string path = string.Empty;
            for (int i = elementNamesCopy.Length - 1; i >= 0; i--)
            {
                if (path.Length > 0)
                    path += "/";
                path += elementNamesCopy[i];
            }
            return path;
        }

        private void cbo_FromMember_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(this.cbo_FromMember.SelectedIndex>=0)
            {
                this.lbl_FromMember.Text = this.cbo_FromMember.SelectedItem.ToString();
            }
        }

        private void btn_GetFields_Click(object sender, EventArgs e)
        {
            this.PopulateSourceFields();
        }
        #endregion

        private void btn_OK_Click(object sender, EventArgs e)
        {
            if(this.cbo_FromMember.SelectedIndex<0)
            {
                MessageBox.Show("select source member");
                this.cbo_FromMember.Focus();
                return;
            }

            DataTable dtNetData=new DataTable();
            List<LinkDataObject> linkDOs=new List<LinkDataObject>();
            Network.ReadData(this._NetworkName, ref dtNetData, ref linkDOs);
            string fromFieldName = this.cbo_FromMember.SelectedItem.ToString();

            SetDataProvider provider = new SetDataProvider();
            provider.SourceType = SetDataSourceType.DB;
            if (rd_Xml.Checked)
                provider.SourceType = SetDataSourceType.XML;
            else if (rd_Excel.Checked)
                provider.SourceType = SetDataSourceType.ExcelFile;
            provider.DataSourcePath = this.txt_DataSourcePath.Text;
            provider.RecordListPath = this.cbo_RecordPath.Text;
            provider.KeyFieldName = this.cbo_Key.Text;
            provider.LabelField = this.cbo_TitleField.Text;
            provider.FieldPaths = new Dictionary<string, string>();
            Dictionary<string, SimpleDataType> selectedFields = new Dictionary<string, SimpleDataType>();
            foreach (DataGridViewRow dr in this.dv_Fields.Rows)
            {
                if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[1].Value != null)
                {
                    DataGridViewCheckBoxCell cellSelct = (DataGridViewCheckBoxCell)dr.Cells[0];
                    bool selected = bool.Parse(cellSelct.Value.ToString());
                    if (selected)
                    {
                        string fieldName = dr.Cells[1].Value.ToString();
                        provider.FieldPaths.Add(fieldName, fieldName);
                        SimpleDataType dbType = (SimpleDataType)Enum.Parse(typeof(SimpleDataType), dr.Cells[2].Value.ToString());
                        selectedFields.Add(fieldName, dbType);
                    }
                }
            }
            string selectSql = string.Empty;
            if(rd_Db.Checked && ck_UseSQL.Checked)
            {
                selectSql = this.rt_SQL.Text;
            }
            DataTable dt = null;
            if(rd_Db.Checked)
            {
                dt = this.GetExternalDataFromDB(
                    dtNetData, fromFieldName, provider,
                    selectSql, this.cbo_FilterField.Text, selectedFields);
            }
            else
            {
                dt = this.GetExternalDataFromFile(
                    dtNetData, fromFieldName, provider,
                    this.cbo_FilterField.Text, selectedFields);   
            }

            if(dt !=null && dt.Rows.Count>0)
            {
                string newNetName = "New network";
                Network network = new Network();
                network.NetworkName = newNetName;
                network.EntityType = (EntityType) this.cbo_NodeTypes.SelectedIndex;
                network.IsInferred = this.ck_InferredLink.Checked;
                network.RelationType = (LinkType) this.cbo_LinkTypes.SelectedIndex;
                network.DataProvider = provider;
                // distance 
                Assembly assembly = this.GetType().Assembly;
                network.DistanceCalculator = null;
                if (!string.IsNullOrEmpty(this.cbo_DistanceCalc.Text))
                {
                    Type distanceCalcType = assembly.GetType(this.cbo_DistanceCalc.Text);
                    if (distanceCalcType != null)
                    {
                        network.DistanceCalculator = (ILinkDistanceCalculator)Activator.CreateInstance(distanceCalcType);
                    }
                }
                network.AttractingForce = (double)this.num_Attract.Value;
                network.RepellingForce = (double)this.num_Repel.Value;
                // NetData
                NetworkDataObject netDO = new NetworkDataObject();
                netDO.DistanceCalcArgumentMappings = new Dictionary<string, string>();
                foreach (DataGridViewRow dr in this.dv_DistCalcArgMapping.Rows)
                {
                    if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[1].Value != null)
                    {
                        string argName = dr.Cells[0].Value.ToString();
                        string fieldName = dr.Cells[1].Value.ToString();
                        netDO.DistanceCalcArgumentMappings.Add(fieldName, argName);
                    }
                }
                netDO.EntityTypeName = "";
                netDO.FieldDataTypes = new Dictionary<string, SimpleDataType>();
                netDO.FieldNameMappings = new Dictionary<string, string>();
                netDO.FilterableFields = new List<string>();
                netDO.LabelFieldName = this.cbo_TitleField.Text;
                netDO.TimeStampField = string.Empty;
                netDO.LinkableFields = new List<string>();
                netDO.PkFieldName = this.cbo_Key.Text;
                netDO.SelfLinkFromFields = new List<string>();
                if (this.cbo_LinkFrom.SelectedIndex >= 0)
                    netDO.SelfLinkFromFields.Add(this.cbo_LinkFrom.Text);
                netDO.SelfLinkToFields = new List<string>();
                if (this.cbo_LinkTo.SelectedIndex >= 0)
                    netDO.SelfLinkToFields.Add(this.cbo_LinkTo.Text);
                netDO.SelfLinkMethod = LinkMethod.Equals;
                if (this.cbo_LinkMethod.SelectedIndex >= 0)
                    netDO.SelfLinkMethod = (LinkMethod)this.cbo_LinkMethod.SelectedIndex;
                foreach (DataGridViewRow dr in this.dv_Fields.Rows)
                {
                    if (!dr.IsNewRow)
                    {
                        bool selected = bool.Parse(dr.Cells[0].Value.ToString());
                        if(selected)
                        {
                            string tgtField = dr.Cells[1].Value.ToString();
                            string dbTypeStr = dr.Cells[2].Value.ToString();
                            SimpleDataType dbType = (SimpleDataType)Enum.Parse(typeof(SimpleDataType), dbTypeStr);
                            bool filterable = bool.Parse(dr.Cells[3].Value.ToString());
                            bool linkable = bool.Parse(dr.Cells[4].Value.ToString());
                            netDO.FieldNameMappings.Add(tgtField, tgtField);
                            netDO.FieldDataTypes.Add(tgtField, dbType);
                            if (filterable)
                                netDO.FilterableFields.Add(tgtField);
                            if (linkable)
                                netDO.LinkableFields.Add(tgtField);
                        }
                    }
                }
                network.NetData = netDO;

                network.Save();

                if (this.ObjectExplored != null)
                {
                    NetworkJoin join = new NetworkJoin();
                    join.AttractingForce = (double) this.num_JoinAttract.Value;
                    join.RepellingForce = (double) this.num_JoinRepel.Value;
                    join.Behavior = (NetworkLinkBehavior) this.cbo_LinkBehavior.SelectedIndex;
                    join.FromNetworkName = this._NetworkName;
                    join.FromObjMemberName = this.cbo_FromMember.Text;
                    join.JoinCalcTypeName = this.cbo_Linkers.Text;
                    join.ToNetworkName = network.NetworkName;
                    join.ToObjMemberName = this.cbo_FilterField.Text;
                    this.ObjectExplored(this._NetworkName, network.NetworkName, join);
                }
            }
        }

        #region data from file
        private DataTable GetExternalDataFromFile(
            DataTable dtNet, 
            string fromFieldName,
            SetDataProvider provider, 
            string filterFieldName,
            Dictionary<string, SimpleDataType> selectedFields)
        {
            DataTable dtFiltered = new DataTable();
            foreach (string fieldName in selectedFields.Keys)
            {
                dtFiltered.Columns.Add(fieldName, SimpleDataTypeUtil.ToSystemType(selectedFields[fieldName]));
            }
            DataTable dt1 = provider.GetDataFromProvider();
            if(dt1 !=null && dt1.Rows.Count>0)
            {
                SqlDbType joinFieldDbType = SqlDbTypeUtil.ToSqlDbType(dtNet.Columns[fromFieldName].DataType);
                foreach (DataRow dr1 in dtNet.Rows)
                {
                    if(dr1[fromFieldName]==null || dr1[fromFieldName]==DBNull.Value)
                        continue;
                    object propValue = dr1[fromFieldName];
                    string val = FieldDataType.ToMssqlDataValue(joinFieldDbType, propValue.ToString());
                    string filterClause = string.Format(
                        "{0}={1}", filterFieldName, val);
                    DataRow[] drs = dt1.Select(filterClause);
                    if(drs !=null && drs.Length>0)
                    {
                        foreach (DataRow dr in drs)
                        {
                            DataRow drNew = dtFiltered.NewRow();
                            foreach (string fieldName in selectedFields.Keys)
                            {
                                drNew[fieldName] = dr[fieldName];
                            }
                            dtFiltered.Rows.Add(drNew);
                        }
                    }
                }
            }
            return dtFiltered;
        }
        #endregion

        #region data from db
        private DataTable GetExternalDataFromDB(
            DataTable dtNet, 
            string fromFieldName,
            SetDataProvider provider, 
            string selectSql,
            string filterFieldName,
            Dictionary<string, SimpleDataType> selectedFields)
        {
            DataTable dt=new DataTable();
            foreach(string fieldName in selectedFields.Keys)
            {
                dt.Columns.Add(fieldName, SimpleDataTypeUtil.ToSystemType(selectedFields[fieldName]));
            }

            SqlConnection conn=new SqlConnection(provider.DataSourcePath);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql;
                if (!string.IsNullOrEmpty(selectSql))
                    sql = selectSql;
                else
                    sql = string.Format("select * from [{0}]", provider.RecordListPath);
                string fieldList = string.Empty;
                foreach(string selectedField in selectedFields.Keys)
                {
                    if (fieldList.Length > 0)
                        fieldList += ",";
                    fieldList += "[" + selectedField + "]";
                }

                SqlDbType joinFieldDbType = SqlDbTypeUtil.ToSqlDbType(dtNet.Columns[fromFieldName].DataType);
                foreach (DataRow dr1 in dtNet.Rows)
                {
                    if(dr1[fromFieldName]==null || dr1[fromFieldName]==DBNull.Value)
                        continue;
                    object propValue = dr1[fromFieldName];
                    string val = FieldDataType.ToMssqlDataValue(joinFieldDbType, propValue.ToString());
                    cmd.CommandText = string.Format(
                        "select {0} from ({1}) as tmp where tmp.{2}={3}",
                        fieldList, sql, filterFieldName, val);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        DataRow dr = dt.NewRow();
                        foreach (string fieldName in selectedFields.Keys)
                        {
                            dr[fieldName] = reader[fieldName];
                        }
                        dt.Rows.Add(dr);
                    }
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
        #endregion

        #region distances
        private void cbo_DistanceCalc_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cbo_RecordPath.SelectedIndex >= 0 && this.cbo_DistanceCalc.SelectedIndex >= 0)
            {
                this.PopulateSourceFields();
            }
        }

        private void PopulateDistanceCalcArgFields(ILinkDistanceCalculator calc, List<string> fields)
        {
            this.dv_DistCalcArgMapping.Rows.Clear();
            foreach (string reqArgName in calc.RequiredArguments.Keys)
            {
                DataGridViewRow dr = new DataGridViewRow();
                DataGridViewTextBoxCell cellArg = new DataGridViewTextBoxCell();
                cellArg.Value = reqArgName;
                dr.Cells.Add(cellArg);

                DataGridViewComboBoxCell cellField = new DataGridViewComboBoxCell();
                foreach (string fieldName in fields)
                {
                    cellField.Items.Add(fieldName);
                }
                dr.Cells.Add(cellField);

                this.dv_DistCalcArgMapping.Rows.Add(dr);
            }
        }
        #endregion

    }
}
